package raojt.day0109.tour.dao.impl;



import raojt.day0109.tour.dao.TourDao;
import raojt.day0109.tour.entity.ScenicSpot;
import raojt.day0109.tour.entity.TourRoute;
import raojt.day0109.tour.entity.TourScenicrelation;
import raojt.day0109.tour.utils.BaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class TourDaoImpl extends BaseDao implements TourDao {
    @Override
    public List<ScenicSpot> selectScenicSpotAll(Map<String, Object> map) {
        List<ScenicSpot> scenicSpotlist = new ArrayList<>();
        ScenicSpot scenicSpot = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT SS_ID,SS_NAME,SS_DURATION,SS_CONTENT FROM T_SCENIC_SPOT ");
        sql.append(" WHERE 1 = 1 ");
        Object id = map.get("scenicId");
        Object name = map.get("scenicName");
        if ((!"".equals(id) & id != null) & name == null) {
            sql.append(" AND SS_ID = ? ORDER BY SS_ID ");
            rs = super.exectuteQuery(sql.toString(), new Object[]{id});
        } else if ((!"".equals(name) & name != null) & id == null) {
            name = "%" + name + "%";
            sql.append(" AND SS_NAME LIKE ? ORDER BY SS_ID ");
            rs = super.exectuteQuery(sql.toString(), new Object[]{name});
        } else {
            sql.append(" ORDER BY SS_ID ");
            rs = super.exectuteQuery(sql.toString());
        }

        try {
            while (rs.next()) {
                scenicSpot = new ScenicSpot();
                scenicSpot.setId(rs.getString("SS_ID"));
                scenicSpot.setName(rs.getString("SS_NAME"));
                scenicSpot.setDuration(rs.getString("SS_DURATION"));
                scenicSpot.setContent(rs.getString("SS_CONTENT"));
                scenicSpotlist.add(scenicSpot);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, rs);
        }
        return scenicSpotlist;
    }

    @Override
    public int addScenicSpot(ScenicSpot scenicSpot) {
        String sql = " INSERT INTO T_SCENIC_SPOT(SS_ID,SS_NAME,SS_DURATION,SS_CONTENT)\n " +
                " VALUES(SEQ_SCENIC_IDS.NEXTVAL,?,?,?) ";
        int addRes = super.executeUpdate(sql, new Object[]{scenicSpot.getName(), scenicSpot.getDuration(), scenicSpot.getContent()});
        return addRes;
    }

    @Override
    public int updateScenicSpot(ScenicSpot scenicSpot) {
        String sql = " UPDATE T_SCENIC_SPOT SET SS_NAME = ?,SS_DURATION = ?,SS_CONTENT = ? WHERE SS_ID = ? ";
        int updateRes = super.executeUpdate(sql, new Object[]{scenicSpot.getName(), scenicSpot.getDuration(), scenicSpot.getContent(), scenicSpot.getId()});
        return updateRes;
    }

    @Override
    public int deleteScenicSpotById(String id) {
        String sql = " DELETE FROM T_SCENIC_SPOT WHERE SS_ID = ? ";
        int deleteRes = super.executeUpdate(sql, new Object[]{id});
        return deleteRes;
    }

    @Override
    public List<TourRoute> selectTourRouteAll(Map<String, Object> map) {
        List<TourRoute> tourRoutelist = new ArrayList<>();
        TourRoute tourRoute = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT TR_ID,TR_NAME,TR_TYPE,TR_PRICE,TR_PHONE,TR_USER,TO_CHAR(CREATE_TIME) FROM T_TOUR_ROUTE ");
        sql.append(" WHERE 1 = 1 ");
        Object id = map.get("tourRouteId");
        Object name = map.get("tourRouteName");
        if ((!"".equals(id) & id != null) & name == null) {
            sql.append(" AND TR_ID = ? ORDER BY TR_ID ");
            rs = super.exectuteQuery(sql.toString(), new Object[]{id});
        } else if ((!"".equals(name) & name != null) & id == null) {
            name = "%" + name + "%";
            sql.append(" AND TR_NAME LIKE ? ORDER BY TR_ID ");
            rs = super.exectuteQuery(sql.toString(), new Object[]{name});
        } else {
            sql.append(" ORDER BY TR_ID ");
            rs = super.exectuteQuery(sql.toString());
        }
        try {
            while (rs.next()) {
                tourRoute = new TourRoute();
                tourRoute.setId(rs.getString("TR_ID"));
                tourRoute.setName(rs.getString("TR_NAME"));
                tourRoute.setType(rs.getString("TR_TYPE"));
                tourRoute.setPrice(rs.getString("TR_PRICE"));
                tourRoute.setPhone(rs.getString("TR_PHONE"));
                tourRoute.setUser(rs.getString("TR_USER"));
                tourRoute.setCreateTime(rs.getString("TO_CHAR(CREATE_TIME)"));
                tourRoutelist.add(tourRoute);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, rs);
        }
        return tourRoutelist;
    }

    @Override
    public int addTourRoute(TourRoute tourRoute) {
        String sql = " INSERT INTO T_TOUR_ROUTE(TR_ID,TR_NAME,TR_TYPE,TR_PRICE,TR_PHONE,TR_USER,CREATE_TIME)\n " +
                " VALUES(SEQ_TOUR_ROUTE_IDS.NEXTVAL,?,?,?,?,?,SYSDATE) ";
        int addRes = super.executeUpdate(sql, new Object[]{tourRoute.getName(), tourRoute.getType(), tourRoute.getPrice(), tourRoute.getPhone(), tourRoute.getUser()});
        return addRes;
    }

    @Override
    public int updateTourRoute(TourRoute tourRoute) {
        String sql = " UPDATE T_TOUR_ROUTE SET TR_NAME = ? , TR_TYPE = ? , TR_PRICE = ? , TR_PHONE = ? , TR_USER = ? , CREATE_TIME = SYSDATE WHERE TR_ID = ? ";
        int updateRes = super.executeUpdate(sql, new Object[]{tourRoute.getName(), tourRoute.getType(), tourRoute.getPrice(), tourRoute.getPhone(), tourRoute.getUser(), tourRoute.getId()});
        return updateRes;
    }

    @Override
    public int deleteTourRouteById(String id) {
        String sql = " DELETE FROM T_TOUR_ROUTE WHERE TR_ID = ? ";
        int deleteRes = super.executeUpdate(sql, new Object[]{id});
        return deleteRes;
    }

    @Override
    public List<TourScenicrelation> selectTourScenicRelation(Map<String, Object> map) {
        List<TourScenicrelation> tourScenicrelations = new ArrayList<>();
        TourScenicrelation tourScenicrelation = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ID,SS_ID,TR_ID FROM T_TOUR_SCENIC_RELATION ");
        rs = super.exectuteQuery(sql.toString());
        try {
            while (rs.next()) {
                tourScenicrelation = new TourScenicrelation();
                tourScenicrelation.setId(rs.getString("ID"));
                tourScenicrelation.setSsId(rs.getString("SS_ID"));
                tourScenicrelation.setTrId(rs.getString("TR_ID"));
                tourScenicrelations.add(tourScenicrelation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, rs);
        }
        return tourScenicrelations;
    }

    @Override
    public int addTourScenicrelation(String ssId, String trId) {
        String sql = " INSERT INTO T_TOUR_SCENIC_RELATION(ID,SS_ID,TR_ID)\n " +
                " VALUES(SEQ_TOUR_SCENIC_RELATION_IDS.NEXTVAL,?,?) ";
        int addRes = super.executeUpdate(sql, new Object[]{ssId, trId});
        return addRes;
    }

    @Override
    public int updateTourScenicRelation(TourScenicrelation tourScenicRelation) {
        int updateRes = 0;
        return updateRes;
    }

    @Override
    public int deleteTourScenicRelation(String id) {
        String sql = " DELETE FROM T_TOUR_SCENIC_RELATION WHERE ID = ? ";
        int deleteRes = super.executeUpdate(sql, new Object[]{id});
        return deleteRes;
    }
}
